﻿using System;
using System.Collections.Generic;
using NHibernate;
using CRC.GestionSalones.Dominio.Clases;

namespace CRC.GestionSalones.Controladores
{
    public class ControladorBaseSalon
    {
        private static String strQryObtenerProvincias = "select p.Id, p.Nombre from Provincia p order by p.Nombre";
        private static String strQryMunicipios = "select m.Id, m.Nombre1 from Municipio m where m.Provincia.Id={0} order by m.Nombre1";
        private static String strQryZonas = "select z.Id, z.Nombre from Zona z order by z.Nombre";
        private static String strQrySupCircuito = "from Persona sup where sup.Tipo.Id=3 order by sup.Nombre, sup.Apellido1, sup.Apellido2";

        private static String strQryTipoPropietario = "select tp.Id, tp.Descripcion from TipoPropiedad tp order by tp.Descripcion";

        public IList<Object[]> todasProvincias() 
        {
            ISession session = null;
            ITransaction tx = null;
            IList<Object[]> res = null;

            try {
                
                session = NHibernate.NHibernateHelper.GetCurrentSession();
                tx = session.BeginTransaction();

                res = session.CreateQuery(strQryObtenerProvincias).List<Object[]>();
                tx.Commit();
                return res;
            }
            catch (HibernateException he) {

                if (tx != null) tx.Rollback();
                session.Close();
                throw he;
            }
        }

        public IList<Object[]> municipiosDeProvincia(int pIntProv) 
        {
            ISession session = null;
            ITransaction tx = null;
            IList<Object[]> res = null;

            try
            {

                session = NHibernate.NHibernateHelper.GetCurrentSession();
                tx = session.BeginTransaction();

                res = session.CreateQuery(String.Format(strQryMunicipios, pIntProv)).List<Object[]>();
                tx.Commit();
                return res;
            }
            catch (HibernateException he)
            {

                if (tx != null) tx.Rollback();
                session.Close();
                throw he;
            }
        }

        public IList<Object[]> todasZonas()
        {
            ISession session = null;
            ITransaction tx = null;
            IList<Object[]> res = null;

            try
            {

                session = NHibernate.NHibernateHelper.GetCurrentSession();
                tx = session.BeginTransaction();

                res = session.CreateQuery(strQryZonas).List<Object[]>();
                tx.Commit();
                return res;
            }
            catch (HibernateException he)
            {

                if (tx != null) tx.Rollback();
                session.Close();
                throw he;
            }
        }

        public IList<Object[]> todosSuperintendentesCircuito() 
        {
            ISession session = null;
            IList<Object[]> res = new List<Object[]>();
            IList<Persona> supers = null;

            try
            {

                session = NHibernate.NHibernateHelper.GetCurrentSession();

                supers = (IList<Persona>)session.CreateQuery(strQrySupCircuito).List<Persona>();
                foreach (Persona p in supers)
                    res.Add(new Object[] { p.Id, p.NombreCompleto });

                return res;
            }
            catch (HibernateException he)
            {
                session.Close();
                throw he;
            }
        }

        public IList<Object[]> todosTipoPropietario() 
        {
            ISession session = null;
            ITransaction tx = null;
            IList<Object[]> res = null;

            try
            {

                session = NHibernate.NHibernateHelper.GetCurrentSession();
                tx = session.BeginTransaction();

                res = session.CreateQuery(strQryTipoPropietario).List<Object[]>();
                tx.Commit();
                return res;
            }
            catch (HibernateException he)
            {

                if (tx != null) tx.Rollback();
                session.Close();
                throw he;
            }
        } 

    }
}
